﻿//创建时间: 2010-10-11
//创建作者: 李 城
//功能说明: 内容模型管理
//最后修改: 2010-10-13 重载save函数 ，增加建立数据库的功能
//          2010-10-13 重载DeleteInfoById函数 ，增加删除数据库的功能
using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;
using System.Data;
using TRPOP.Common;
using TRPOP.Config;
using TRPOP.CMS.Model;
using TRPOP.CMS.DALFactory;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace TRPOP.CMS.DALSQLServer
{
    public class ModelDAL : IModelDAL
    {

        #region 保存 Save
        
        /*原来的保存方法
         * 
        public EnumSubmitResult Save(ModelInfo info)
        {
            

            string strStoreProcedure = SystemInfoHelper.getSystemInfo().DBprefixTableName + "_" + "Model_Save";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);

            odbDataBase.AddInParameter(odbCommand, "@Name", System.Data.DbType.String, info.Name);
            odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, info.TableName);
            odbDataBase.AddInParameter(odbCommand, "@Description", System.Data.DbType.String, info.Description);
            odbDataBase.AddInParameter(odbCommand, "@AddTime", System.Data.DbType.DateTime, info.AddTime);
            odbDataBase.AddInParameter(odbCommand, "@AddUserId", System.Data.DbType.Int32, info.AddUserId);
            odbDataBase.AddInParameter(odbCommand, "@UpdateTime", System.Data.DbType.DateTime, info.UpdateTime);
            odbDataBase.AddInParameter(odbCommand, "@UpdateUserId", System.Data.DbType.Int32, info.UpdateUserId);
            odbDataBase.AddParameter(odbCommand, "@ModelId", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, info.ModelId);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                info.ModelId = (int)odbDataBase.GetParameterValue(odbCommand, "@ModelId");
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        */

        public EnumSubmitResult Save(ModelInfo info)
        {
            EnumSubmitResult enmResult = EnumSubmitResult.Failed;

            #region 建立数据表
            string strSQL = "";
            if (info.ModelId > 0)
            {
                //ALERT
                //修改模型并不能修改 数据表名
                enmResult = EnumSubmitResult.Success;
            }
            else
            {
                strSQL = " CREATE TABLE [dbo].{0}(  {1}" +
                               "{0}id        int         IDENTITY(1,1), {1}" +
                               "CONSTRAINT PK{0} PRIMARY KEY NONCLUSTERED ({0}id) {1}" +
                           ") {1}" +
                           " {1}" +
                           "if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', '" + SystemInfoHelper.getSystemInfo().DBOwner + "', 'table', '{0}', 'column', '{0}id')) {1}" +
                           "BEGIN {1}" +
                           "    exec sys.sp_dropextendedproperty 'MS_Description', 'schema',  '" + SystemInfoHelper.getSystemInfo().DBOwner + "', 'table', '{0}', 'column', '{0}id' {1}" +
                           "END {1}" +
                           "    exec sys.sp_addextendedproperty  'MS_Description', '信息标识', 'schema', '" + SystemInfoHelper.getSystemInfo().DBOwner + "', 'table', '{0}', 'column', '{0}id' {1}" +
                           "";
                strSQL = string.Format(strSQL, info.TableName ,  System.Environment.NewLine );
                SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
                DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSQL);
                try
                {
                    odbDataBase.ExecuteNonQuery(odbCommand);
                    enmResult = EnumSubmitResult.Success;
                }
                catch //(Exception oeException)
                {
                    //throw new Exception(oeException.Message, oeException);
                }
            }
            #endregion

            if (enmResult == EnumSubmitResult.Success)
            {
                #region 向数据表里添加数据
                string strStoreProcedure = SystemInfoHelper.getSystemInfo().DBprefixTableName + "_" + "Model_Save";
                SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
                DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
                odbDataBase.AddInParameter(odbCommand, "@Name", System.Data.DbType.String, info.Name);
                odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, info.TableName);
                odbDataBase.AddInParameter(odbCommand, "@Description", System.Data.DbType.String, info.Description);
                odbDataBase.AddInParameter(odbCommand, "@AddTime", System.Data.DbType.DateTime, info.AddTime);
                odbDataBase.AddInParameter(odbCommand, "@AddUserId", System.Data.DbType.Int32, info.AddUserId);
                odbDataBase.AddInParameter(odbCommand, "@UpdateTime", System.Data.DbType.DateTime, info.UpdateTime);
                odbDataBase.AddInParameter(odbCommand, "@UpdateUserId", System.Data.DbType.Int32, info.UpdateUserId);
                odbDataBase.AddParameter(odbCommand, "@ModelId", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, info.ModelId);
                try
                {
                    odbDataBase.ExecuteNonQuery(odbCommand);
                    info.ModelId = (int)odbDataBase.GetParameterValue(odbCommand, "@ModelId");
                    enmResult = EnumSubmitResult.Success;
                }
                catch (Exception oeException)
                {
                    throw new Exception(oeException.Message, oeException);
                }
                #endregion
            }
            return enmResult;
        }
        #endregion

        #region 获取实体 GetInfoById
        public ModelInfo GetInfoById(int id)
        {
            string strSqlString = "SELECT * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_model_Model WHERE ModelId=" + id;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            ModelInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    if (reader.Read())
                    {
                        oInfo = new ModelInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }
        #endregion

        #region 删除 DeleteInfoById
        /*
        public EnumSubmitResult DeleteInfoById(int id)
        {
            return DeleteInfoByIds(id.ToString());
        }
         */
        public EnumSubmitResult DeleteInfoById(int id)
        {
            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            string strSQL = " IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SystemInfoHelper.getSystemInfo().DBOwner + "].[{0}]') AND type in (N'U')) {1}" +
                            " DROP TABLE [" + SystemInfoHelper.getSystemInfo().DBOwner + "].[{0}] ";
            ModelInfo info = GetInfoById(id);
            if (info != null)
            {
                strSQL = string.Format(strSQL, info.TableName, System.Environment.NewLine);
                SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
                DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSQL);
                try
                {
                    odbDataBase.ExecuteNonQuery(odbCommand);
                    enmResult = EnumSubmitResult.Success;
                }
                catch //(Exception oeException)
                {
                    enmResult = EnumSubmitResult.Failed;
                    //throw new Exception(oeException.Message, oeException);
                }
            }
            if (enmResult == EnumSubmitResult.Success)
                enmResult = DeleteInfoByIds(id.ToString());
            return enmResult;
        }
        #endregion

        #region 删除 DeleteInfoByIds
        public EnumSubmitResult DeleteInfoByIds(string ids)
        {
            string strSqlString = "DELETE FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_model_Model WHERE ModelId in(" + ids + ")";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion

        #region 自定义列表（无分页） GetListByCondition
        public List<ModelInfo> GetListByCondition(int topn, string condition, string orderby)
        {
            string strSqlString = "SELECT ";
            if (topn > 0)
            {
                strSqlString += " TOP " + topn;
            }
            strSqlString += " * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_model_Model WHERE 1=1"; if (condition != "")
            {
                strSqlString += condition;
            }
            if (orderby != "")
            {
                strSqlString += " ORDER BY " + orderby;
            }
            else
            {
                strSqlString += " ORDER BY ModelId DESC";
            }

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            List<ModelInfo> olstInfo = new List<ModelInfo>(); ModelInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new ModelInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 自定义列表（分页） GetListByPager
        public List<ModelInfo> GetListByPager(int pageNumber, int pageSize, string condition, string orderby, out int recordCount)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().PagerStoreProcedureName;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
            List<ModelInfo> olstInfo = new List<ModelInfo>(); ModelInfo oInfo = null;
            odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, SystemInfoHelper.getSystemInfo().DBprefixTableName + "_model_Model");
            odbDataBase.AddInParameter(odbCommand, "@ShowColumns", System.Data.DbType.String, "*");
            odbDataBase.AddInParameter(odbCommand, "@pageNumber", System.Data.DbType.Int32, pageNumber);
            odbDataBase.AddInParameter(odbCommand, "@pageSize", System.Data.DbType.Int32, pageSize);
            odbDataBase.AddInParameter(odbCommand, "@condition", System.Data.DbType.String, condition);
            odbDataBase.AddInParameter(odbCommand, "@orderby", System.Data.DbType.String, orderby);
            odbDataBase.AddOutParameter(odbCommand, "@recordCount", System.Data.DbType.Int32, 0);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new ModelInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
                recordCount = int.Parse(odbCommand.Parameters["@RecordCount"].Value.ToString());
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 获取最大Id
        /// <summary>
        /// 获取最大Id
        /// </summary>
        /// <returns></returns>
        public int GetMaxId()
        {
            int intMaxId = 0;
            string strSqlString = "SELECT ISNULL( Max(ModelId),0)   AS 'ModelId'  FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_model_Model ";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            ModelInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    if (reader.Read())
                    {
                        oInfo = new ModelInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            if (oInfo != null)
            {
                intMaxId = oInfo.ModelId;
            }
            return intMaxId;
        }
        #endregion
    }
}
